package com.matt.foundation.sqlOperator;

import java.sql.*;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/8/11
 * *******************************
 */
public class ObservationOperator {

    static final String remoteUrl = "jdbc:mysql://dev.mattcd.com:3306/matt_app";
    static final String remoteName = "root";
    static final String remotePWd = "FQs088";

    static final String localUrl = "jdbc:mysql://localhost:3306/vega";
    static final String localName = "root";
    static final String localPWd = "123456";

    public static void main(String[] args) {
//        observerTransfer();
//        observationOptionTransfer();
//        observationTypeTransfer();
    }

    public static void observerTransfer(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select * from M_TestStoreObsPoint";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(localUrl, localName, localPWd);
            PreparedStatement localPreparedStatement = null;
            ResultSet localResultSet = null;
            while (remoteResultSet.next()){
                Long id = remoteResultSet.getLong(1); //id
                Long planId = remoteResultSet.getLong(2); //教案id
                Long typeId = remoteResultSet.getLong(3); //观测类型id
                String title =  remoteResultSet.getString(4); //题目
                Date created = remoteResultSet.getDate(5);//创建时间
                Boolean isDeleted = remoteResultSet.getBoolean(6);//是否删除

                System.out.println("id :" + id);
                System.out.println("planId :" +planId);
                System.out.println("typeId :" +typeId);
                System.out.println("title :" + title);
                System.out.println("created :" + created);
                System.out.println("isDeleted :" + isDeleted);


                String insert = "insert into observation (id, title, typeId, planId, created, deleted) values( ?, ?, ?, ?, ?, ?)";
                localPreparedStatement = localConn.prepareStatement(insert);
                localPreparedStatement.setLong(1, id);
                localPreparedStatement.setString(2, title);
                localPreparedStatement.setLong(3, typeId);
                localPreparedStatement.setLong(4, planId);
                localPreparedStatement.setDate(5, created);
                localPreparedStatement.setBoolean(6, isDeleted);

                localPreparedStatement.execute();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void observationOptionTransfer(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select * from M_TestStoreOptions";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(localUrl, localName, localPWd);
            PreparedStatement localPreparedStatement = null;
            ResultSet localResultSet = null;
            while (remoteResultSet.next()){
                Long id = remoteResultSet.getLong(1); //id
                Long observationId = remoteResultSet.getLong(2); //观测id
                String title =  remoteResultSet.getString(3); //题目
                Integer score = remoteResultSet.getInt(4); //分数
                Date created = remoteResultSet.getDate(5);//创建时间
                Boolean isDeleted = remoteResultSet.getBoolean(6);//是否删除

                System.out.println("id :" + id);
                System.out.println("planId :" +observationId);
                System.out.println("title :" + title);
                System.out.println("score :" +score);
                System.out.println("created :" + created);
                System.out.println("isDeleted :" + isDeleted);


                String insert = "insert into observation_option (id, title, score, observationId, created, deleted) values( ?, ?, ?, ?, ?, ?)";
                localPreparedStatement = localConn.prepareStatement(insert);
                localPreparedStatement.setLong(1, id);
                localPreparedStatement.setString(2, title);
                localPreparedStatement.setInt(3, score);
                localPreparedStatement.setLong(4, observationId);
                localPreparedStatement.setDate(5, created);
                localPreparedStatement.setBoolean(6, isDeleted);

                localPreparedStatement.execute();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void observationTypeTransfer(){

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection remoteConn = DriverManager.getConnection(remoteUrl, remoteName, remotePWd);
            PreparedStatement remotePreparedStatement = null;
            ResultSet remoteResultSet = null ;
            String query = "select * from M_TestType";
            remotePreparedStatement = remoteConn.prepareStatement(query);

            remoteResultSet = remotePreparedStatement.executeQuery();
            Connection localConn = DriverManager.getConnection(localUrl, localName, localPWd);
            PreparedStatement localPreparedStatement = null;
            ResultSet localResultSet = null;
            while (remoteResultSet.next()){
                Long id = remoteResultSet.getLong(1); //id
                String name = remoteResultSet.getString(2); //观测id
                String description =  remoteResultSet.getString(3); //题目
                Date created = remoteResultSet.getDate(4);//创建时间
                Boolean isDeleted = remoteResultSet.getBoolean(5);//是否删除

                System.out.println("id :" + id);
                System.out.println("name :" +name);
                System.out.println("description :" + description);
                System.out.println("created :" + created);
                System.out.println("isDeleted :" + isDeleted);


                String insert = "insert into observation_type (id, name, description, created, deleted) values( ?, ?, ?, ?, ?)";
                localPreparedStatement = localConn.prepareStatement(insert);
                localPreparedStatement.setLong(1, id);
                localPreparedStatement.setString(2, name);
                localPreparedStatement.setString(3, description);
                localPreparedStatement.setDate(4, created);
                localPreparedStatement.setBoolean(5, isDeleted);

                localPreparedStatement.execute();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }



}
